/**
 * 认证系统数据库结构定义
 */

export interface User {
  id: string;
  username: string;
  email: string;
  password_hash: string;
  role: 'super_admin' | 'admin' | 'editor' | 'user' | 'guest';
  avatar?: string;
  display_name?: string;
  phone?: string;
  status: 'active' | 'inactive' | 'banned';
  email_verified: boolean;
  last_login?: string;
  login_count: number;
  created_at: string;
  updated_at: string;
}

export interface Session {
  id: string;
  user_id: string;
  token: string;
  refresh_token?: string;
  expires_at: string;
  ip_address?: string;
  user_agent?: string;
  created_at: string;
  updated_at: string;
}

export interface UserProfile {
  user_id: string;
  bio?: string;
  website?: string;
  location?: string;
  birth_date?: string;
  gender?: 'male' | 'female' | 'other';
  preferences: Record<string, any>;
  created_at: string;
  updated_at: string;
}

export interface LoginAttempt {
  id: string;
  email: string;
  ip_address: string;
  success: boolean;
  error_message?: string;
  created_at: string;
}

// 权限管理相关接口
export interface Permission {
  id: string;
  name: string;
  display_name: string;
  description?: string;
  resource_type: 'page' | 'api' | 'data';
  resource_path?: string;
  resource_id?: string;
  action: 'read' | 'write' | 'delete' | 'execute';
  category: string;
  is_system?: boolean;
  is_active: boolean;
  created_at: string;
  updated_at: string;
}

export interface Role {
  id: string;
  name: string;
  display_name: string;
  description?: string;
  level: number;
  is_system: boolean;
  is_active: boolean;
  created_at: string;
  updated_at: string;
}

export interface RolePermission {
  id: string;
  role_id: string;
  permission_id: string;
  granted_by?: string;
  granted_at: string;
}

export interface UserRole {
  id: string;
  user_id: string;
  role_id: string;
  assigned_by?: string;
  assigned_at: string;
  expires_at?: string;
}

export interface UserPermission {
  id: string;
  user_id: string;
  permission_id: string;
  granted: boolean;
  granted_by?: string;
  granted_at: string;
  expires_at?: string;
}

export interface PagePermission {
  id: string;
  path_pattern: string;
  required_permission?: string;
  required_role?: string;
  allow_anonymous: boolean;
  description?: string;
  is_active: boolean;
  created_at: string;
  updated_at: string;
}

/**
 * 数据库表创建SQL语句
 */
export const AUTH_TABLES_SQL = {
  users: `
    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      username TEXT UNIQUE NOT NULL,
      email TEXT UNIQUE NOT NULL,
      password_hash TEXT NOT NULL,
      role TEXT DEFAULT 'user' CHECK (role IN ('super_admin', 'admin', 'editor', 'user', 'guest')),
      avatar TEXT,
      display_name TEXT,
      phone TEXT,
      status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'banned')),
      email_verified BOOLEAN DEFAULT FALSE,
      last_login TEXT,
      login_count INTEGER DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `,
  
  sessions: `
    CREATE TABLE IF NOT EXISTS sessions (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      token TEXT UNIQUE NOT NULL,
      refresh_token TEXT,
      expires_at TEXT NOT NULL,
      ip_address TEXT,
      user_agent TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )
  `,
  
  user_profiles: `
    CREATE TABLE IF NOT EXISTS user_profiles (
      user_id TEXT PRIMARY KEY,
      bio TEXT,
      website TEXT,
      location TEXT,
      birth_date TEXT,
      gender TEXT CHECK (gender IN ('male', 'female', 'other')),
      preferences TEXT DEFAULT '{}',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )
  `,
  
  login_attempts: `
    CREATE TABLE IF NOT EXISTS login_attempts (
      id TEXT PRIMARY KEY,
      email TEXT NOT NULL,
      ip_address TEXT NOT NULL,
      success BOOLEAN NOT NULL,
      error_message TEXT,
      created_at TEXT NOT NULL
    )
  `,

  // 权限管理表
  permissions: `
    CREATE TABLE IF NOT EXISTS permissions (
      id TEXT PRIMARY KEY,
      name TEXT UNIQUE NOT NULL,
      display_name TEXT NOT NULL,
      description TEXT,
      resource_type TEXT NOT NULL CHECK (resource_type IN ('page', 'api', 'data')),
      resource_path TEXT,
      resource_id TEXT,
      action TEXT NOT NULL CHECK (action IN ('read', 'write', 'delete', 'execute')),
      category TEXT DEFAULT 'general',
      is_system BOOLEAN DEFAULT FALSE,
      is_active BOOLEAN DEFAULT TRUE,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `,

  roles: `
    CREATE TABLE IF NOT EXISTS roles (
      id TEXT PRIMARY KEY,
      name TEXT UNIQUE NOT NULL,
      display_name TEXT NOT NULL,
      description TEXT,
      level INTEGER DEFAULT 0,
      is_system BOOLEAN DEFAULT FALSE,
      is_active BOOLEAN DEFAULT TRUE,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `,

  role_permissions: `
    CREATE TABLE IF NOT EXISTS role_permissions (
      id TEXT PRIMARY KEY,
      role_id TEXT NOT NULL,
      permission_id TEXT NOT NULL,
      granted_by TEXT,
      granted_at TEXT NOT NULL,
      FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
      FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
      UNIQUE(role_id, permission_id)
    )
  `,

  user_roles: `
    CREATE TABLE IF NOT EXISTS user_roles (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      role_id TEXT NOT NULL,
      assigned_by TEXT,
      assigned_at TEXT NOT NULL,
      expires_at TEXT,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
      UNIQUE(user_id, role_id)
    )
  `,

  user_permissions: `
    CREATE TABLE IF NOT EXISTS user_permissions (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      permission_id TEXT NOT NULL,
      granted BOOLEAN DEFAULT TRUE,
      granted_by TEXT,
      granted_at TEXT NOT NULL,
      expires_at TEXT,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
      UNIQUE(user_id, permission_id)
    )
  `,

  page_permissions: `
    CREATE TABLE IF NOT EXISTS page_permissions (
      id TEXT PRIMARY KEY,
      path_pattern TEXT NOT NULL,
      required_permission TEXT,
      required_role TEXT,
      allow_anonymous BOOLEAN DEFAULT FALSE,
      description TEXT,
      is_active BOOLEAN DEFAULT TRUE,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    )
  `
};

/**
 * 索引创建SQL语句
 */
export const AUTH_INDEXES_SQL = [
  // 用户相关索引
  'CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)',
  'CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)',
  'CREATE INDEX IF NOT EXISTS idx_users_status ON users(status)',
  'CREATE INDEX IF NOT EXISTS idx_users_role ON users(role)',
  
  // 会话相关索引
  'CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token)',
  'CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id)',
  'CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at)',
  
  // 登录尝试索引
  'CREATE INDEX IF NOT EXISTS idx_login_attempts_email ON login_attempts(email)',
  'CREATE INDEX IF NOT EXISTS idx_login_attempts_ip ON login_attempts(ip_address)',
  'CREATE INDEX IF NOT EXISTS idx_login_attempts_created_at ON login_attempts(created_at)',
  
  // 权限管理索引
  'CREATE INDEX IF NOT EXISTS idx_permissions_name ON permissions(name)',
  'CREATE INDEX IF NOT EXISTS idx_permissions_resource_type ON permissions(resource_type)',
  'CREATE INDEX IF NOT EXISTS idx_permissions_category ON permissions(category)',
  'CREATE INDEX IF NOT EXISTS idx_permissions_is_active ON permissions(is_active)',
  
  // 角色索引
  'CREATE INDEX IF NOT EXISTS idx_roles_name ON roles(name)',
  'CREATE INDEX IF NOT EXISTS idx_roles_level ON roles(level)',
  'CREATE INDEX IF NOT EXISTS idx_roles_is_system ON roles(is_system)',
  'CREATE INDEX IF NOT EXISTS idx_roles_is_active ON roles(is_active)',
  
  // 角色权限关联索引
  'CREATE INDEX IF NOT EXISTS idx_role_permissions_role_id ON role_permissions(role_id)',
  'CREATE INDEX IF NOT EXISTS idx_role_permissions_permission_id ON role_permissions(permission_id)',
  
  // 用户角色关联索引
  'CREATE INDEX IF NOT EXISTS idx_user_roles_user_id ON user_roles(user_id)',
  'CREATE INDEX IF NOT EXISTS idx_user_roles_role_id ON user_roles(role_id)',
  'CREATE INDEX IF NOT EXISTS idx_user_roles_expires_at ON user_roles(expires_at)',
  
  // 用户权限索引
  'CREATE INDEX IF NOT EXISTS idx_user_permissions_user_id ON user_permissions(user_id)',
  'CREATE INDEX IF NOT EXISTS idx_user_permissions_permission_id ON user_permissions(permission_id)',
  'CREATE INDEX IF NOT EXISTS idx_user_permissions_expires_at ON user_permissions(expires_at)',
  
  // 页面权限索引
  'CREATE INDEX IF NOT EXISTS idx_page_permissions_path_pattern ON page_permissions(path_pattern)',
  'CREATE INDEX IF NOT EXISTS idx_page_permissions_required_role ON page_permissions(required_role)',
  'CREATE INDEX IF NOT EXISTS idx_page_permissions_is_active ON page_permissions(is_active)'
];

/**
 * 初始化认证数据库表
 */
export async function initAuthTables(db: any): Promise<void> {
  try {
    // 对于当前的数据库系统，我们使用集合(collection)而不是SQL表
    // 确保认证相关的集合存在
    const collections = ['users', 'sessions', 'user_profiles', 'login_attempts'];
    
    for (const collection of collections) {
      // 通过创建一个临时记录来确保集合存在，然后删除它
      const tempResult = await db.create(collection, { _temp: true });
      if (tempResult.success && tempResult.data?.id) {
        await db.delete(collection, { id: tempResult.data.id });
      }
      console.log(`✅ 初始化集合: ${collection}`);
    }
    
    console.log('✅ 认证数据库初始化完成');
    
    // 创建默认管理员账户（如果不存在）
    await createDefaultAdmin(db);
    
  } catch (error) {
    console.error('❌ 初始化认证数据库失败:', error);
    throw error;
  }
}

/**
 * 创建默认管理员账户
 */
async function createDefaultAdmin(db: any): Promise<void> {
  const adminEmail = 'admin@admin.com';
  
  // 检查管理员是否已存在
  const existingAdmin = await db.findOne('users', { email: adminEmail });
  
  if (!existingAdmin.success || !existingAdmin.data) {
    const bcrypt = require('bcryptjs');
    const adminId = `admin_${Date.now()}`;
    const defaultPassword = 'admin123456'; // 生产环境中应该使用更安全的密码
    const hashedPassword = await bcrypt.hash(defaultPassword, 12);
    const now = new Date().toISOString();
    
    // 创建管理员用户
    const adminUser = {
      id: adminId,
      username: 'admin',
      email: adminEmail,
      password_hash: hashedPassword,
      role: 'admin',
      display_name: '系统管理员',
      status: 'active',
      email_verified: true,
      login_count: 0,
      created_at: now,
      updated_at: now
    };
    
    await db.create('users', adminUser);
    
    // 创建管理员资料
    const adminProfile = {
      user_id: adminId,
      bio: 'AIGC教学设计平台系统管理员',
      preferences: { theme: 'dark', language: 'zh-CN' },
      created_at: now,
      updated_at: now
    };
    
    await db.create('user_profiles', adminProfile);
    
    console.log(`✅ 创建默认管理员账户: ${adminEmail} / ${defaultPassword}`);
  } else {
    console.log('✅ 默认管理员账户已存在');
  }
}

/**
 * 初始化权限管理系统数据
 */
export async function initializePermissionSystem(db: any): Promise<void> {
  try {
    const now = new Date().toISOString();
    
    // 创建默认角色
    const defaultRoles = [
      {
        id: `role_${Date.now()}_1`,
        name: 'super_admin',
        display_name: '超级管理员',
        description: '拥有系统所有权限的超级管理员',
        level: 100,
        is_system: true
      },
      {
        id: `role_${Date.now()}_2`,
        name: 'admin',
        display_name: '管理员',
        description: '系统管理员，拥有大部分管理权限',
        level: 80,
        is_system: true
      },
      {
        id: `role_${Date.now()}_3`,
        name: 'editor',
        display_name: '编辑者',
        description: '内容编辑者，可以管理内容相关功能',
        level: 60,
        is_system: true
      },
      {
        id: `role_${Date.now()}_4`,
        name: 'user',
        display_name: '普通用户',
        description: '普通用户，拥有基本使用权限',
        level: 20,
        is_system: true
      },
      {
        id: `role_${Date.now()}_5`,
        name: 'guest',
        display_name: '访客',
        description: '访客用户，权限受限',
        level: 10,
        is_system: true
      }
    ];

    // 插入默认角色
    for (const role of defaultRoles) {
      const existing = await db.findOne('roles', { name: role.name });
      if (!existing.success || !existing.data) {
        await db.create('roles', {
          ...role,
          is_active: true,
          created_at: now,
          updated_at: now
        });
      }
    }

    // 创建默认权限
    const defaultPermissions = [
      // 用户管理权限
      { name: 'user.read', display_name: '查看用户', description: '查看用户列表和详情', resource_type: 'data', action: 'read', category: 'user_management', is_system: true },
      { name: 'user.write', display_name: '编辑用户', description: '创建和编辑用户信息', resource_type: 'data', action: 'write', category: 'user_management', is_system: true },
      { name: 'user.delete', display_name: '删除用户', description: '删除用户账户', resource_type: 'data', action: 'delete', category: 'user_management', is_system: true },
      
      // 角色管理权限
      { name: 'role.read', display_name: '查看角色', description: '查看角色列表和详情', resource_type: 'data', action: 'read', category: 'role_management', is_system: true },
      { name: 'role.write', display_name: '编辑角色', description: '创建和编辑角色', resource_type: 'data', action: 'write', category: 'role_management', is_system: true },
      { name: 'role.delete', display_name: '删除角色', description: '删除角色', resource_type: 'data', action: 'delete', category: 'role_management', is_system: true },
      
      // 权限管理权限
      { name: 'permission.read', display_name: '查看权限', description: '查看权限列表和详情', resource_type: 'data', action: 'read', category: 'permission_management', is_system: true },
      { name: 'permission.write', display_name: '编辑权限', description: '创建和编辑权限', resource_type: 'data', action: 'write', category: 'permission_management', is_system: true },
      { name: 'permission.delete', display_name: '删除权限', description: '删除权限', resource_type: 'data', action: 'delete', category: 'permission_management', is_system: true },
      
      // 页面访问权限
      { name: 'page.admin', display_name: '访问管理后台', description: '访问管理员后台页面', resource_type: 'page', resource_path: '/admin', action: 'read', category: 'page_access', is_system: true },
      { name: 'page.user_management', display_name: '访问用户管理', description: '访问用户管理页面', resource_type: 'page', resource_path: '/admin/users', action: 'read', category: 'page_access', is_system: true },
      { name: 'page.role_management', display_name: '访问角色管理', description: '访问角色管理页面', resource_type: 'page', resource_path: '/admin/roles', action: 'read', category: 'page_access', is_system: true },
      { name: 'page.permission_management', display_name: '访问权限管理', description: '访问权限管理页面', resource_type: 'page', resource_path: '/admin/permissions', action: 'read', category: 'page_access', is_system: true },
      
      // AI功能权限
      { name: 'ai.text_to_image', display_name: '文生图功能', description: '使用文生图AI功能', resource_type: 'api', resource_path: '/api/ai/text-to-image', action: 'execute', category: 'ai_features', is_system: false },
      { name: 'ai.image_to_image', display_name: '图生图功能', description: '使用图生图AI功能', resource_type: 'api', resource_path: '/api/ai/image-to-image', action: 'execute', category: 'ai_features', is_system: false },
      { name: 'ai.chat', display_name: 'AI对话功能', description: '使用AI对话功能', resource_type: 'api', resource_path: '/api/ai/chat', action: 'execute', category: 'ai_features', is_system: false }
    ];

    // 插入默认权限
    for (const permission of defaultPermissions) {
      const existing = await db.findOne('permissions', { name: permission.name });
      if (!existing.success || !existing.data) {
        await db.create('permissions', {
          id: `perm_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`,
          ...permission,
          is_active: true,
          created_at: now,
          updated_at: now
        });
      }
    }

    // 创建默认页面权限配置
    const defaultPagePermissions = [
      { path_pattern: '/admin/*', required_role: 'admin', description: '管理员后台页面' },
      { path_pattern: '/admin/users/*', required_permission: 'page.user_management', description: '用户管理页面' },
      { path_pattern: '/admin/roles/*', required_permission: 'page.role_management', description: '角色管理页面' },
      { path_pattern: '/admin/permissions/*', required_permission: 'page.permission_management', description: '权限管理页面' },
      { path_pattern: '/profile/*', required_role: 'user', description: '用户个人资料页面' },
      { path_pattern: '/', allow_anonymous: true, description: '首页' },
      { path_pattern: '/login', allow_anonymous: true, description: '登录页面' },
      { path_pattern: '/register', allow_anonymous: true, description: '注册页面' }
    ];

    // 插入默认页面权限
    for (const pagePermission of defaultPagePermissions) {
      const existing = await db.findOne('page_permissions', { path_pattern: pagePermission.path_pattern });
      if (!existing.success || !existing.data) {
        await db.create('page_permissions', {
          id: `page_perm_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`,
          ...pagePermission,
          allow_anonymous: pagePermission.allow_anonymous || false,
          is_active: true,
          created_at: now,
          updated_at: now
        });
      }
    }

    console.log('✅ 权限管理系统初始化完成');
  } catch (error) {
    console.error('❌ 初始化权限管理系统失败:', error);
    throw error;
  }
}